How to: Create a parameter query to prompt user for criteria.
Solution:
Create a new query in Design view, add the desired tables, and type prompts in the 'Criteria' row of the query design grid for the desired fields.
1) If the Database window is not active, activate the Database window.
2) Click the 'Queries' tab in the Database window.
Queries tab
3) Click 'New'. (The New Query dialog box appears.)
4) Select 'Design View' from the list box.
5) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
6) Do one of the following:
a) Click the 'Tables' tab to display a list of tables only.
b) Click the 'Queries' tab to display a list of queries only.
c) Click the 'Both' tab to display a list of both tables and queries.
7) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
8) Click 'Add'.
9) Click 'Close' when all of the desired tables and/or queries have been added.
10) For the field that is to have a parameter, type the following in the 'Criteria' row:
[prompt]
(where 'prompt' is the question or statement that appears asking the user to input the parameter).
Prompt
NOTE 1: The prompt text cannot be the same as the field name, but it can include the field name.
NOTE 2: To use two prompts to specify a range of values, type 'Between [initial value in range] And [final value in range]' in the field's 'Criteria' row.
EXAMPLE: For the query to return the records between two dates, type the following in the date field: Between [Enter the beginning date:] And [Enter the ending date:]
11) Repeat step 10) for each field that is to have a parameter.
12) (Special Case) If a crosstab query or chart is based on this parameter query, do the following to set a data type for the parameter(s) typed in the 'Criteria' row above:
a) Select the 'Query' menu and select 'Parameters...'. (The Query Parameters dialog box appears.)
b) In the first row of the 'Parameter' column, type the prompt typed in step 10) for the first parameter field.
c) Click in the 'Data Type' column for the same row. (A down arrow appears.)
d) Click on the arrow. (A drop-down list appears.)
e) Select the desired data type from the 'Data Type' drop-down list box.
NOTE: Access offers a suggestion in the 'Data Type' column.
f) Repeat steps 12)b) through 12)e) for each field with a parameter for which to set the data type.
g) Click 'OK'.
13) To preview the parameter query:
a) Select the 'View' menu and select 'Datasheet'. (The Enter Parameter Value dialog box appears.)
b) Type the desired value for the parameter in the '<prompt>' box, where <prompt> is the prompt typed in the 'Criteria' row in steps 10) and 11).
c) Click 'OK'. (The records meeting the criteria typed above in step 13)b) appear in the Datasheet view.)
NOTE: For a range parameter (such as a date range), a second dialog box appears. Type the value, and click 'OK'.
14) To run the parameter query:
a) Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
b) Select the 'Query' menu and select 'Run'. (The Enter Parameter Value dialog box appears.)
c) Type the desired value for the parameter in the '<prompt>' box, where <prompt> is the prompt typed in the 'Criteria' row in steps 10) and 11).
d) Click 'OK'. (The records meeting the criteria typed above in step 14)c) appear in the Datasheet view.)
NOTE: For a range parameter (such as a date range), a second dialog box appears. Type the value, and click 'OK'.
15) To save the query:
a) Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
b) Type a name for the parameter query in the 'Query Name' box.
c) Click 'OK'.
16) Select the 'File' menu and select 'Close' to close the Query Design view.
